package com.headline.dao;

import com.headline.common.Log;
import com.headline.common.PageObject;
import com.headline.dbutil.DbManager;
import com.headline.model.UserEntity;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * Created with Administrator
 * DATE:2016/11/4
 * Time:16:53
 */
public class UserDao {
    static Log log = Log.getLogger();

    public int insertUser(UserEntity userEntity) {
        int n = 0;
        String sql = " insert into [user] (nickname,password) values(?,?)";
        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            Object[] params = {userEntity.getNickname(), userEntity.getPassword()};
            n = queryRunner.update(conn, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("写入失败:" + e.getMessage());
        }
        DbUtils.closeQuietly(conn);
        return n;
    }

    public List<UserEntity> queryList(String strWhere) {
        List<UserEntity> list = null;
        String sql = " select * from [user] where 1=1 ";
        if (strWhere != "") {
            sql += strWhere;
        }

        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        try {
            list = queryRunner.query(conn, sql, new BeanListHandler<UserEntity>(UserEntity.class));
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("查询失败:" + e.getMessage());
        }
        return list;
    }

    public UserEntity queryUserById(int id) {
        UserEntity userEntity = null;
        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from  [user] where id=?";
        Object params[] = {id};
        try {
            userEntity = (UserEntity) queryRunner.query(conn, sql, params, new BeanHandler(UserEntity.class));
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("查询失败:" + e.getMessage());
        }
        return userEntity;
    }

    public int updateUser(UserEntity userEntity) {
        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "update [user] set [nickname]= ? , [password]=? where [id] = ?";
        Object params[] = {userEntity.getNickname(), userEntity.getPassword(), userEntity.getId()};
        try {

            int result = queryRunner.update(conn, sql, params);
            DbUtils.closeQuietly(conn);
            return result;

        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("更新失败:" + e.getMessage());
        }
        return 0;
    }

    public int deleteUserById(int id) {
        Connection conn = DbManager.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "delete from [user] where id =?";
        try {
            int delResult = queryRunner.update(conn, sql, id);
            DbUtils.closeQuietly(conn);
            return delResult;
        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("删除失败:" + e.getMessage());
        }
        return 0;
    }

    public PageObject queryPage(String strWhere, int pageIndex, int pageSize) {

        PageObject pageObject = new PageObject();
        Connection conn = DbManager.getConnection();
        String sql_1 = "select count(1) from [user]";
        String sql_2 = "SELECT TOP " + pageSize + " * " +
                "FROM ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM dbo.[user]) as A WHERE RowNumber > " + pageSize + "*(" + pageIndex + "-1) ";

        QueryRunner queryRunner = new QueryRunner();
        try {
            Object o = queryRunner.query(conn, sql_1, new ScalarHandler());
            int rows = Integer.parseInt(o.toString());
            int pageCount = rows / pageSize + (rows % pageSize == 0 ? 0 : 1);
            List<UserEntity> list = queryRunner.query(conn, sql_2, new BeanListHandler<UserEntity>(UserEntity.class));
            pageObject.setAbsolutePage(pageCount);
            pageObject.setCurrentPage(pageIndex);
            pageObject.setDatasource(list);
            pageObject.setPageSize(pageSize);
            pageObject.setTotalCount(rows);
        } catch (SQLException e) {
            e.printStackTrace();
            log.logger.error("分页查询失败:" + e.getMessage());
        }
        return pageObject;

    }
}
